package com.hydx.vat.dao;

import java.math.BigDecimal;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.hydx.vat.entity.ListData;

public interface ListDataDao extends JpaRepository<ListData, Integer> {

	List<ListData> findByCommodityId(Integer commodityId);

	// 获取类别所有金额
	@Query("SELECT SUM(u.allPrice) FROM ListData u where u.commodityId =:companyId and u.smallClassId=:smallClassId")
	Double getGoodProportion(@Param("companyId") Integer companyId, @Param("smallClassId") Integer smallClassId);

	// 获取类别所有纳税金额
	@Query("SELECT SUM(u.vatMoney) FROM ListData u where u.commodityId =:commodityId and u.smallClassId=:smallClassId")
	Double getGoodVatProportion(@Param("commodityId") Integer commodityId, @Param("smallClassId") int smallClassId);

	//获取数量
	@Query("SELECT SUM(u.commodityAmount) FROM ListData u where u.commodityId =:commodityId and u.smallClassId=:smallClassId")
	Integer getSmallClassUnit(@Param("commodityId") Integer commodityId, @Param("smallClassId") int smallClassId);

	//获取小类别数量
	@Query(value = "SELECT SUM(commodityAmount) FROM ListData tmptable WHERE (smallClassId=:smallClassId AND commodityId=:commodityId) AND vatdate LIKE :year% GROUP BY DATE_FORMAT(vatdate,'%Y-%m')", nativeQuery = true)
	List<Integer> getSmallClassUnit(@Param("commodityId") Integer commodityId, @Param("smallClassId") int smallClassId,
			@Param("year") String year);

	// 获取小类别的销售額
	@Query("SELECT SUM(u.allPrice) FROM ListData u where u.commodityId =:commodityId and u.smallClassId=:smallClassId")
	Double getSmallClassMoney(@Param("commodityId") Integer commodityId, @Param("smallClassId") int smallClassId);

	// 获取小类别的销售数量
	@Query("SELECT SUM(u.commodityAmount) FROM ListData u where u.commodityId =:commodityId and u.smallClassId=:smallClassId")
	Integer getSmallClassAccount(@Param("commodityId") Integer commodityId, @Param("smallClassId") int smallClassId);

	// 获取小类别的销售数量按按照月份分组
	@Query(value = "SELECT SUM(commodityAmount) FROM listdata  WHERE commodityId =ANY(SELECT commodityId FROM commodity WHERE incompanyId=:companyId) AND smallClassId=:smallClassId  GROUP BY DATE_FORMAT(vatdate,'%m')", nativeQuery = true)
	List<BigDecimal> getSmallClassAccountByMonth(@Param("companyId") Integer companyId,
			@Param("smallClassId") int smallClassId);

	// 获取小类别的销售数量的月份分组
	@Query(value = "SELECT DATE_FORMAT(vatdate,'%m') FROM listdata  WHERE commodityId =ANY(SELECT commodityId FROM commodity WHERE incompanyId=:companyId) AND smallClassId=:smallClassId  GROUP BY DATE_FORMAT(vatdate,'%m')", nativeQuery = true)
	List<String> getSmallClassMonth(@Param("companyId") Integer companyId, @Param("smallClassId") int smallClassId);

	// 获取小类别的税率
	@Query("SELECT SUM(u.vatMoney) FROM ListData u where u.commodityId =:commodityId and u.smallClassId=:smallClassId")
	Double getSmallClassVat(@Param("commodityId") Integer commodityId, @Param("smallClassId") int smallClassId);

	// 获取大类别的营业数额
	@Query("SELECT SUM(u.allPrice) FROM ListData u where u.commodityId =:commodityId and u.largeClassId=:largeClassId")
	Integer getLargeClassMoney(@Param("commodityId") Integer commodityId, @Param("largeClassId") int largeClassId);

	// 获取大类别的数额
	@Query("SELECT SUM(u.commodityAmount) FROM ListData u where u.commodityId =:commodityId and u.largeClassId=:largeClassId")
	Integer getLargeClassAccount(@Param("commodityId") Integer commodityId, @Param("largeClassId") int largeClassId);

	// 获取小类别的单价
	@Query(value = "SELECT price FROM listdata  where commodityId =:commodityId and largeClassId=:largeClassId group by price", nativeQuery = true)
	Double getSmallClassPlace(@Param("commodityId") Integer commodityId, @Param("largeClassId") int largeClassId);

	// 获取大类别的税率
	@Query("SELECT SUM(u.vatMoney) FROM ListData u where u.commodityId =:commodityId and u.largeClassId=:largeClassId")
	Integer getLargeClassVat(@Param("commodityId") Integer commodityId, @Param("largeClassId") int largeClassId);

	// 获取获取公司月份
	@Query(value = "SELECT listId FROM listdata  WHERE commodityId=?1 and smallClassId=?2", nativeQuery = true)
	Integer getMonthBycompanyId(Integer commodityId, Integer smallClassId);
}
